package com.aplink.generic.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.text.TextUtils;

import com.aplink.generic.controller.GenericApplication;
import com.aplink.generic.interfaces.DatabaseDelete;
import com.aplink.generic.interfaces.DatabaseGet;
import com.aplink.generic.interfaces.DatabaseInsert;
import com.aplink.generic.interfaces.DatabaseUpdate;
import com.aplink.generic.pojo.ContentData;
import com.aplink.generic.pojo.GenericAttribute;
import com.aplink.generic.pojo.GenericObject;
import com.aplink.generic.pojo.MasterConfig;
import com.aplink.generic.pojo.ObjPropInfo;
import com.aplink.generic.pojo.TableRelationship;
import com.aplink.generic.pojo.TableSyncData;
import com.aplink.generic.sharepreference.GenericSharePreference;
import com.aplink.generic.util.LogUtil;
import com.aplink.generic.util.convertcount.GenericFilter;
import com.aplink.generic.webservice.DataResult;
import com.aplink.generic.webservice.HttpResponseStatus;
import com.aplink.generic.webservice.ResponseStatus;
import com.aplink.generic.xmlparser.ContentDataParser;

public class DatabaseHelper implements DatabaseInsert, DatabaseGet,
        DatabaseUpdate, DatabaseDelete {
    private static final int GET_ALL = -1;
    private static DatabaseHelper sInstance = null;

    public static DatabaseHelper getInstance() {
        if (DatabaseHelper.sInstance == null) {
            DatabaseHelper.sInstance = new DatabaseHelper();
            if ((DatabaseHelper.sInstance.mDatabase == null)
                    || !DatabaseHelper.sInstance.mDatabase.isOpen()) {
                DatabaseHelper.sInstance.mDatabase = DatabaseManager
                        .getInstance().getWritableDatabase();
                DatabaseHelper.sInstance.mCoreDatabaseHelper = new CoreDatabaseHelper(
                        DatabaseHelper.sInstance.mDatabase);
            }
        }
        return DatabaseHelper.sInstance;
    }

    private final boolean mAllowSubmitTransaction = true;
    private CoreDatabaseHelper mCoreDatabaseHelper;

    private SQLiteDatabase mDatabase;

    private String baseDelete(final ContentData objPropCol,
            final ContentData objForeignCol, final GenericObject genericObject) {
        if (objForeignCol.getForiegnKeys().isEmpty()) {
            return this.mCoreDatabaseHelper.deleteData(objForeignCol,
                    genericObject);
        }
        for (final ObjPropInfo objPropInfo : objForeignCol.getForiegnKeys()) {
            if (objPropInfo.getRelationship() != null) {
                if (objPropInfo.getRelationship().equals(
                        TableRelationship.ONE_TO_MANY)) {
                    // if one-to-may-> insert main record--> insert foreigns
                    final String realRowID = this.mCoreDatabaseHelper
                            .deleteData(objForeignCol, genericObject);
                    if (realRowID != null) {
                        final ObjPropInfo primaryKey = objForeignCol
                                .getPrimaryKey(objForeignCol.getMainTable());
                        if (primaryKey.isFKey()
                                && (primaryKey.getForeignTable() != null)) {
                            final String foriegnTableName = primaryKey
                                    .getForeignTable();
                            final ContentData foriegnTable = GenericFilter
                                    .getObjPropColOf(foriegnTableName,
                                            objPropCol);
                            baseDelete(objPropCol, foriegnTable, genericObject);
                        }
                        return realRowID;
                    }
                } else if (objPropInfo.getRelationship().equals(
                        TableRelationship.MANY_TO_ONE)) {
                    // if many-to-one -> insert foreign, with each foreign
                    // insert main record.
                    final String mainTableName = objPropInfo.getTable();
                    final ContentData mainTableCol = GenericFilter
                            .getObjPropColOf(mainTableName, objPropCol);
                    final String foriegnTableName = objPropInfo
                            .getForeignTable();
                    final ContentData foriegnTable = GenericFilter
                            .getObjPropColOf(foriegnTableName, objPropCol);
                    // Insert foreign;
                    final String realRowID = this.mCoreDatabaseHelper
                            .deleteData(foriegnTable, genericObject);
                    if (realRowID != null) {
                        this.mCoreDatabaseHelper.deleteData(mainTableCol,
                                genericObject);
                    }
                }
            }
        }
        return null;
    }

    private long baseInitTable(final GenericObject object,
            final String tableName) {
        try {
            final ContentValues cv = new ContentValues();
            for (int i = 0; i < object.size(); i++) {
                final GenericAttribute attribute = object.valueAt(i);
                final String key = attribute.getName().toString();
                final String value = attribute.getValue().toString();
                if (value != null) {
                    cv.put(key, value);
                }
            }
            return this.mDatabase.insert(tableName, null, cv);
        } catch (final SQLException e) {
            throw e;
        }
    }

    private String baseInsert(final ContentData objPropCol,
            final ContentData objForeignCol, final GenericObject genericObject) {
        if (objForeignCol.getForiegnKeys().isEmpty()) {
            return this.mCoreDatabaseHelper.insertData(objForeignCol,
                    genericObject);
        }
        for (final ObjPropInfo objPropInfo : objForeignCol.getForiegnKeys()) {
            if (objPropInfo.getRelationship() != null) {
                if (objPropInfo.getRelationship().equals(
                        TableRelationship.ONE_TO_MANY)) {
                    // if one-to-may-> insert main record--> insert foreigns

                    final String realRowID = this.mCoreDatabaseHelper
                            .insertData(objForeignCol, genericObject);
                    if (realRowID != null) {
                        final ObjPropInfo primaryKey = objForeignCol
                                .getPrimaryKey(objForeignCol.getMainTable());
                        if (primaryKey.isFKey()
                                && (primaryKey.getForeignTable() != null)) {
                            final String foriegnTableName = primaryKey
                                    .getForeignTable();
                            final ContentData foriegnTable = GenericFilter
                                    .getObjPropColOf(foriegnTableName,
                                            objPropCol);
                            genericObject.put(
                                    primaryKey.getForeignFullColumnName(),
                                    realRowID);
                            baseInsert(objPropCol, foriegnTable, genericObject);
                        }
                        return realRowID;
                    }
                } else if (objPropInfo.getRelationship().equals(
                        TableRelationship.MANY_TO_ONE)) {
                    // if many-to-one -> insert foreign, with each foreign
                    // insert main record.
                    final String mainTableName = objPropInfo.getTable();
                    final ContentData mainTableCol = GenericFilter
                            .getObjPropColOf(mainTableName, objPropCol);
                    final String foriegnTableName = objPropInfo
                            .getForeignTable();
                    final ContentData foriegnTable = GenericFilter
                            .getObjPropColOf(foriegnTableName, objPropCol);
                    // Insert foreign;
                    final String realRowID = this.mCoreDatabaseHelper
                            .insertData(foriegnTable, genericObject);
                    if (realRowID != null) {
                        genericObject.put(objPropInfo.getFullColumnName(),
                                realRowID);
                        // Insert main record;
                        this.mCoreDatabaseHelper.insertData(mainTableCol,
                                genericObject);
                    }
                }
            }
        }
        return null;
    }

    private String baseUpdate(final ContentData objPropCol,
            final ContentData objForeignCol, final GenericObject genericObject) {
        if (objForeignCol.getForiegnKeys().isEmpty()) {
            return this.mCoreDatabaseHelper.updateData(objForeignCol,
                    genericObject);
        }
        for (final ObjPropInfo objPropInfo : objForeignCol.getForiegnKeys()) {
            if (objPropInfo.getRelationship() != null) {
                if (objPropInfo.getRelationship().equals(
                        TableRelationship.ONE_TO_MANY)) {
                    // if one-to-may-> insert main record--> insert foreigns
                    final String realRowID = this.mCoreDatabaseHelper
                            .updateData(objForeignCol, genericObject);
                    if (realRowID != null) {
                        final ObjPropInfo primaryKey = objForeignCol
                                .getPrimaryKey(objForeignCol.getMainTable());
                        if (primaryKey.isFKey()
                                && (primaryKey.getForeignTable() != null)) {
                            final String foriegnTableName = primaryKey
                                    .getForeignTable();
                            final ContentData foriegnTable = GenericFilter
                                    .getObjPropColOf(foriegnTableName,
                                            objPropCol);
                            genericObject.get(primaryKey.getFullColumnName())
                                    .setValue(realRowID);
                            baseUpdate(objPropCol, foriegnTable, genericObject);
                        }
                        return realRowID;
                    }
                } else if (objPropInfo.getRelationship().equals(
                        TableRelationship.MANY_TO_ONE)) {
                    // if many-to-one -> insert foreign, with each foreign
                    // insert main record.
                    final String mainTableName = objPropInfo.getTable();
                    final ContentData mainTableCol = GenericFilter
                            .getObjPropColOf(mainTableName, objPropCol);
                    final String foriegnTableName = objPropInfo
                            .getForeignTable();
                    final ContentData foriegnTable = GenericFilter
                            .getObjPropColOf(foriegnTableName, objPropCol);
                    // Insert foreign;
                    final String realRowID = this.mCoreDatabaseHelper
                            .updateData(foriegnTable, genericObject);
                    if (realRowID != null) {
                        genericObject.get(objPropInfo.getFullColumnName())
                                .setValue(realRowID);
                        // Insert main record;
                        this.mCoreDatabaseHelper.updateData(mainTableCol,
                                genericObject);
                    }
                }
            }
        }
        return null;
    }

    private void beginTransaction() {
        if (this.mDatabase.isOpen()) {
            if (!this.mDatabase.inTransaction()) {
                this.mDatabase.beginTransaction();
            }
        }
    }

    private String buildWhereCondition(final ContentData objPropCol,
            final GenericObject genericObject) {
        final String table = objPropCol.getMainTable();
        final ObjPropInfo createdTime = objPropCol.getCreatedTime(table);
        final SqlMethods sqlMethods = SqlMethods.getInstance();
        if (createdTime == null) {
            final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(table);
            if (!TextUtils.isEmpty(genericObject.getValue(primaryKey
                    .getFullColumnName()))) {
                final String value = genericObject.getValue(
                        primaryKey.getFullColumnName()).toString();
                sqlMethods.equal(primaryKey, value);
            }
        } else {
            final String value = genericObject.getValue(
                    createdTime.getFullColumnName()).toString();
            sqlMethods.equal(createdTime, value);
        }
        return sqlMethods.toString();
    }

    @Override
    public void clearSyncStatus() {
        try {
            final MasterConfig masterConfig = GenericApplication.getInstance()
                    .getMasterConfig();
            beginTransaction();
            for (int i = 0; i < masterConfig.getWebserviceConfig()
                    .getTableDownloads().size(); i++) {
                final TableSyncData tableSyncData = masterConfig
                        .getWebserviceConfig().getTableDownloads().valueAt(i);

                final int contentDataFileId = tableSyncData
                        .getWebserviceFunctionID();
                final ContentData objPropCol = ContentDataParser.getInstance()
                        .parseIntoObjProp(contentDataFileId);

                for (final ObjPropInfo objPropInfo : objPropCol.getSyncStatus()) {
                    final String mainTableName = objPropInfo.getTable();
                    final ObjPropInfo syncStatusColumn = objPropCol
                            .getSyncStatus(mainTableName);
                    final ContentValues contentValues = new ContentValues();
                    contentValues.putNull(syncStatusColumn.getColumnName());
                    final SqlMethods whereClause = SqlMethods.getInstance()
                            .equal(syncStatusColumn, "delete");
                    this.mDatabase.delete(mainTableName,
                            whereClause.toString(), null);
                    final int rowEffected = this.mDatabase.update(
                            mainTableName, contentValues, null, null);
                    if (rowEffected > 0) {
                        LogUtil.info(LogUtil.TAG_DATABASE,
                                "clear sync status for table:" + mainTableName,
                                "clearSyncStatus", getClass());
                    }
                }
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            submitTransaction();
        }
    }

    @Override
    public boolean clearSyncStatus(final ArrayList<DataResult> dataResults) {
        if (dataResults.isEmpty()) {
            return true;
        }
        try {
            for (final DataResult result : dataResults) {
                if (result.getServerResponse().equals(
                        HttpResponseStatus.SUCCESS)) {
                    if (result.getResponseStatus().getStatus() == ResponseStatus.SUCCESS) {
                        final GenericObject object = result.getGenericObject();
                        final ContentData objPropCol = result.getContentData();
                        final String mainTableName = objPropCol.getMainTable();
                        final ObjPropInfo syncColumn = objPropCol
                                .getSyncStatus(mainTableName);
                        final String action = object.getValue(syncColumn);
                        final String where = buildWhereCondition(objPropCol,
                                object);
                        if (action.equals("delete")) {
                            this.delete(objPropCol, where);
                        } else {
                            final ContentValues values = new ContentValues();
                            values.putNull(syncColumn.getColumnName());
                            this.mDatabase.update(mainTableName, values, where,
                                    null);
                        }
                    }
                }
            }
            return true;
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Clear SyncStatus object", getClass());
        } finally {
            submitTransaction();
        }
        return false;
    }

    @Override
    public void clearSyncStatus(final GenericObject object,
            final ContentData objPropCol) {
        try {
            beginTransaction();
            for (final ObjPropInfo objPropInfo : objPropCol.getSyncStatus()) {
                final String mainTableName = objPropInfo.getTable();
                final ObjPropInfo syncStatusColumn = objPropCol
                        .getSyncStatus(mainTableName);
                final ContentValues contentValues = new ContentValues();
                contentValues.putNull(syncStatusColumn.getColumnName());
                final SqlMethods whereClause = SqlMethods.getInstance().equal(
                        syncStatusColumn, "delete");
                this.mDatabase.delete(mainTableName, whereClause.toString(),
                        null);
                final int rowEffected = this.mDatabase.update(mainTableName,
                        contentValues, null, null);
                if (rowEffected > 0) {
                    LogUtil.info(LogUtil.TAG_DATABASE,
                            "clear sync status for table:" + mainTableName,
                            "clearSyncStatus");
                }
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            submitTransaction();
        }
    }

    @Override
    public String delete(final ContentData objPropCol) {
        try {
            beginTransaction();
            String id = null;
            for (final ObjPropInfo objPropInfo : objPropCol.getForiegnKeys()) {
                id = String.valueOf(this.mDatabase.delete(
                        objPropInfo.getTable(), null, null));
            }
            this.mDatabase.delete(objPropCol.getMainTable(), null, null);
            return id;
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "delete object", getClass());
        } finally {
            submitTransaction();
        }
        return null;
    }

    @Override
    public ArrayList<String> delete(final ContentData objPropCol,
            final ArrayList<GenericObject> genericObjects) {
        final ArrayList<String> ids = new ArrayList<String>();
        try {
            beginTransaction();
            final String mainTable = objPropCol.getMainTable();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            for (final GenericObject object : genericObjects) {
                final String id = baseDelete(objPropCol, mainTableProperty,
                        object);
                if (id == null) {
                    LogUtil.info(LogUtil.TAG_DATABASE, " can't delete "
                            + object.getTableName() + "  to database",
                            "deleteList");
                } else {
                    LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                            + " was delete, id=" + id, "deleteList");
                    ids.add(id);
                }
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "delete object", getClass());
        } finally {
            submitTransaction();
        }
        return ids;
    }

    @Override
    public void delete(final ContentData objPropCol, final String sqlMethods) {
        try {
            beginTransaction();
            final String mainTable = objPropCol.getMainTable();
            this.mDatabase.delete(mainTable, sqlMethods, null);
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "delete object", getClass());
        } finally {
            submitTransaction();
        }
    }

    @Override
    public String delete(final GenericObject object,
            final ContentData objPropCol) {
        try {
            beginTransaction();
            final String mainTable = objPropCol.getMainTable();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            final String id = baseDelete(objPropCol, mainTableProperty, object);
            if (id == null) {
                LogUtil.info(LogUtil.TAG_DATABASE,
                        " can't delete " + object.getTableName()
                                + "  to database", "delete");
            } else {
                LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                        + " was delete, id=" + id, "delete");
            }
            return id;
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "delete object", getClass());
        } finally {
            submitTransaction();
        }
        return null;
    }

    @Override
    public String delete(final String table) {
        if (table != null) {
            try {
                beginTransaction();
                return String.valueOf(this.mDatabase.delete(table, null, null));
            } catch (final SQLException e) {
                e.printStackTrace();
            } finally {
                submitTransaction();
            }
        }
        return null;
    }

    /**
     * return an Generic object with where clause conditions. If object is not
     * exists, return null.
     */
    @Override
    public GenericObject get(final ContentData propCol, final String whereClause) {
        try {
            final ArrayList<GenericObject> genericObjects = this.mCoreDatabaseHelper
                    .getData(propCol, whereClause, 1, true);
            if (genericObjects.isEmpty()) {
                return null;
            }
            return this.mCoreDatabaseHelper.getData(propCol, whereClause, 1)
                    .get(0);
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "get",
                    getClass());
        }
        return null;
    }

    /**
     * return an Generic object with where clause conditions. If object is not
     * exists, return null.
     */
    @Override
    public GenericObject get(final ContentData propCol,
            final String whereClause, final boolean isCheckDataRules) {
        try {
            final ArrayList<GenericObject> genericObjects = this.mCoreDatabaseHelper
                    .getData(propCol, whereClause, 1, isCheckDataRules);
            if (genericObjects.isEmpty()) {
                return null;
            }
            return genericObjects.get(0);
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "get",
                    getClass());
        }
        return null;
    }

    @Override
    public ArrayList<GenericObject> getList(final ContentData propCol,
            String whereClause) {
        try {
            if ((whereClause == null) || whereClause.isEmpty()) {
                whereClause = null;
            }
            return this.mCoreDatabaseHelper.getData(propCol, whereClause,
                    DatabaseHelper.GET_ALL);
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "getList",
                    getClass());
        }
        return new ArrayList<GenericObject>();
    }

    @Override
    public ArrayList<String> initTable(
            final ArrayList<GenericObject> genericObjects) {
        final String tableName = genericObjects.get(0).getTableName();
        return this.initTable(genericObjects, tableName);
    }

    @Override
    public ArrayList<String> initTable(
            final ArrayList<GenericObject> genericObjects, final String table) {
        final ArrayList<String> ids = new ArrayList<String>();
        if ((table == null) || (genericObjects.size() == 0)) {
            return null;
        }
        try {
            beginTransaction();
            this.mDatabase.delete(table, null, null);
            for (final GenericObject object : genericObjects) {
                final long id = baseInitTable(object, table);
                if (id != -1) {
                    ids.add(String.valueOf(id));
                    LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                            + " was inserted, id=" + id, "insertObject");
                } else {
                    LogUtil.info(LogUtil.TAG_DATABASE, "Can't not insert "
                            + object.getTableName(), "insertObject");
                }
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        } finally {
            submitTransaction();
        }
        return ids;
    }

    @Override
    public String initTable(final GenericObject object, final String table) {
        if (object.size() == 0) {
            return null;
        }
        try {
            beginTransaction();
            final String id = String.valueOf(baseInitTable(object, table));
            if (id != null) {
                LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                        + " was init, id=" + id, "initTable");
            } else {
                LogUtil.info(LogUtil.TAG_DATABASE,
                        " can't init " + object.getTableName()
                                + "  to database", "initTable");
            }
            return id;
        } catch (final SQLException e) {
            e.printStackTrace();
        } finally {
            submitTransaction();
        }
        return null;
    }

    @Override
    public void initTables(final ArrayList<DataResult> dataResults) {
        final boolean isOfflineMode = GenericSharePreference.getInstance()
                .isOfflineMode();
        try {
            beginTransaction();
            GenericSharePreference.getInstance().setOfflineMode(false);
            for (final DataResult dataResult : dataResults) {
                if (dataResult.getServerResponse().equals(
                        HttpResponseStatus.SUCCESS)) {
                    if (dataResult.getContentData() == null) {
                        final ArrayList<GenericObject> genericObject = dataResult
                                .getData();
                        if (genericObject.isEmpty()) {
                            continue;
                        }
                        final String tableName = genericObject.get(0)
                                .getTableName();
                        this.mDatabase.delete(tableName, null, null);
                        for (final GenericObject object : genericObject) {
                            final long id = baseInitTable(object, tableName);
                            if (id != -1) {
                                LogUtil.info(LogUtil.TAG_DATABASE,
                                        object.getTableName()
                                                + " was inserted, id=" + id,
                                        "insertObject");
                            } else {
                                LogUtil.info(
                                        LogUtil.TAG_DATABASE,
                                        "Can't not insert "
                                                + object.getTableName(),
                                        "insertObject");
                            }
                        }
                    } else {
                        final ArrayList<GenericObject> genericObjects = dataResult
                                .getData();
                        if (genericObjects.isEmpty()) {
                            continue;
                        }
                        final ContentData contentData = dataResult
                                .getContentData();
                        final String mainTable = contentData.getMainTable();
                        final ContentData mainTableProperty = GenericFilter
                                .getObjPropColOf(mainTable, contentData);

                        for (final GenericObject object : genericObjects) {
                            final String id = baseInsert(contentData,
                                    mainTableProperty, object);
                            if (id == null) {
                                LogUtil.info(
                                        LogUtil.TAG_DATABASE,
                                        " can't insert "
                                                + object.getTableName()
                                                + "  to database",
                                        "insertObject");
                            } else {
                                LogUtil.info(LogUtil.TAG_DATABASE,
                                        object.getTableName()
                                                + " was inserted, id=" + id,
                                        "insertObject");
                            }
                        }
                    }
                }
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "initTables",
                    getClass());
        } finally {
            GenericSharePreference.getInstance().setOfflineMode(isOfflineMode);
            submitTransaction();
        }
    }

    @Override
    public ArrayList<String> insert(
            final ArrayList<GenericObject> genericObjects,
            final ContentData objPropCol) {
        final ArrayList<String> ids = new ArrayList<String>();
        try {
            beginTransaction();
            final String mainTable = objPropCol.getMainTable();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            for (final GenericObject object : genericObjects) {
                final String id = baseInsert(objPropCol, mainTableProperty,
                        object);
                if (id == null) {
                    LogUtil.info(LogUtil.TAG_DATABASE, " can't insert "
                            + object.getTableName() + "  to database",
                            "insertObject");
                } else {
                    LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                            + " was inserted, id=" + id, "insertObject");
                    ids.add(id);
                }
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            submitTransaction();
        }
        return ids;
    }

    @Override
    public String insert(final GenericObject object,
            final ContentData objPropCol) {
        try {
            final String mainTable = objPropCol.getMainTable();
            beginTransaction();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            final String id = baseInsert(objPropCol, mainTableProperty, object);

            if (id == null) {
                LogUtil.info(LogUtil.TAG_DATABASE,
                        " can't insert " + object.getTableName()
                                + "  to database", "insertObject");
            } else {
                LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                        + " was inserted, id=" + id, "insertObject");
            }
            return id;
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            submitTransaction();
        }
        return null;
    }

    @Override
    public String insert(final GenericObject object,
            final ContentData objPropCol,
            final ArrayList<GenericObject> listImages,
            final ContentData galleryContentData) {
        try {
            final String mainTable = objPropCol.getMainTable();
            beginTransaction();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            final String id = baseInsert(objPropCol, mainTableProperty, object);

            if (id == null) {
                LogUtil.info(LogUtil.TAG_DATABASE,
                        " can't insert " + object.getTableName()
                                + "  to database", "insertObject");
            } else {
                LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                        + " was inserted, id=" + id, "insertObject");
            }
            final String column = galleryContentData.getMainTable() + "."
                    + objPropCol.getPrimaryKey(mainTable).getColumnName();
            for (final GenericObject genericObject : listImages) {
                genericObject.put(column, id);
                baseInsert(galleryContentData, galleryContentData,
                        genericObject);
            }
            return id;
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            submitTransaction();
        }
        return null;
    }

    @Override
    public String insertDownloaded(final DataResult dataResult) {
        final boolean isOfflineMode = GenericSharePreference.getInstance()
                .isOfflineMode();
        try {
            GenericSharePreference.getInstance().setOfflineMode(false);
            final ArrayList<GenericObject> genericObjects = dataResult
                    .getData();
            final ContentData contentData = dataResult.getContentData();
            final String mainTable = contentData.getMainTable();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, contentData);
            final MasterConfig masterConfig = GenericApplication.getInstance()
                    .getMasterConfig();
            final TableSyncData tableSyncData = masterConfig
                    .getWebserviceConfig().getTableDownloads().get(mainTable);
            boolean isCheckExists = true;
            if (tableSyncData.isClearAll()) {
                final ArrayList<GenericObject> objects = getList(contentData,
                        null);
                this.delete(contentData, objects);
                isCheckExists = false;
            }
            beginTransaction();
            final ArrayList<String> ids = new ArrayList<String>();
            for (final GenericObject object : genericObjects) {
                if (isCheckExists) {
                    if (this.isExists(object, mainTableProperty)) {
                        continue;
                    }
                }
                final String id = baseInsert(contentData, mainTableProperty,
                        object);
                if (id == null) {
                    LogUtil.info(LogUtil.TAG_DATABASE, " can't insert "
                            + object.getTableName() + "  to database",
                            "insertObject");
                } else {
                    LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                            + " was inserted, id=" + id, "insertObject");
                    ids.add(id);
                }
            }
            if (ids.isEmpty()) {
                return null;
            }
            return ids.get(0);
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            GenericSharePreference.getInstance().setOfflineMode(isOfflineMode);
            submitTransaction();
        }
        return null;
    }

    @Override
    public ArrayList<String> insertDownloadedList(
            final ArrayList<DataResult> dataResults) {
        final boolean isOfflineMode = GenericSharePreference.getInstance()
                .isOfflineMode();
        final ArrayList<String> ids = new ArrayList<String>();
        try {
            GenericSharePreference.getInstance().setOfflineMode(false);
            beginTransaction();
            for (final DataResult dataResult : dataResults) {
                final ArrayList<GenericObject> genericObjects = dataResult
                        .getData();
                final ContentData contentData = dataResult.getContentData();
                final String mainTable = contentData.getMainTable();
                final ContentData mainTableProperty = GenericFilter
                        .getObjPropColOf(mainTable, contentData);

                for (final GenericObject object : genericObjects) {
                    if (this.isExists(object, mainTableProperty)) {
                        continue;
                    }
                    final String id = baseInsert(contentData,
                            mainTableProperty, object);
                    if (id == null) {
                        LogUtil.info(LogUtil.TAG_DATABASE, " can't insert "
                                + object.getTableName() + "  to database",
                                "insertObject");
                    } else {
                        LogUtil.info(LogUtil.TAG_DATABASE,
                                object.getTableName() + " was inserted, id="
                                        + id, "insertObject");
                        ids.add(id);
                    }
                }
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            GenericSharePreference.getInstance().setOfflineMode(isOfflineMode);
            submitTransaction();
        }
        return ids;
    }

    @Override
    public boolean isExists(final ContentData objPropCol,
            final String whereClause) {
        try {
            return this.mCoreDatabaseHelper.isExists(objPropCol, whereClause);
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "getList",
                    getClass());
        } finally {
            submitTransaction();
        }
        return false;
    }

    @Override
    public boolean isExists(final GenericObject genericObject,
            final ContentData objPropCol) {
        try {
            return this.mCoreDatabaseHelper.isExists(genericObject, objPropCol);
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "getList",
                    getClass());
        }
        return false;
    }

    public void releaseDatabase() {
        if ((this.mDatabase != null) && this.mDatabase.isOpen()) {
            if (this.mDatabase.inTransaction()) {
                if (this.mAllowSubmitTransaction) {
                    this.mDatabase.setTransactionSuccessful();
                    this.mDatabase.endTransaction();
                }
            }
            DatabaseManager.getInstance().close();
            DatabaseHelper.sInstance = null;
        }
    }

    @Override
    public ArrayList<GenericObject> selectDistinct(final String table,
            final ContentData objPropCol, final String whereClause) {
        try {
            if ((objPropCol == null) || objPropCol.isEmpty()) {
                return null;
            }
            final ArrayList<String> queriedColumns = new ArrayList<String>();
            for (int i = 0; i < objPropCol.size(); i++) {
                final ObjPropInfo objPropInfo = objPropCol.valueAt(i);
                if (objPropInfo.getTable().equalsIgnoreCase(table)) {
                    queriedColumns.add(objPropInfo.getFullColumnName());
                }
            }
            final ArrayList<GenericObject> genericObjectList = new ArrayList<GenericObject>();
            Cursor cursor;
            String queryTables = null;
            if (objPropCol.getJoins().isEmpty()) {
                queryTables = table;
            } else {
                queryTables = objPropCol.getJoins();
            }
            final String queryString = SQLiteQueryBuilder.buildQueryString(
                    true, queryTables,
                    queriedColumns.toArray(new String[queriedColumns.size()]),
                    whereClause, objPropCol.getGroupBy(), null,
                    objPropCol.getOderBy(), null);
            cursor = this.mDatabase.rawQuery(queryString, null);
            LogUtil.info(LogUtil.TAG_DATABASE, queryString, "buildQueryString");

            if (cursor != null) {
                cursor.moveToFirst();
                final int numRows = cursor.getCount();
                for (int i = 0; i < numRows; ++i) {
                    final GenericObject genericObject = new GenericObject();
                    genericObject.setTableName(table);
                    for (int j = 0; j < objPropCol.size(); j++) {
                        final ObjPropInfo objPropInfo = objPropCol.valueAt(j);
                        if (objPropInfo.getTable().equalsIgnoreCase(table)) {
                            final String name = objPropInfo.getColumnName();
                            String value = cursor.getString(cursor
                                    .getColumnIndex(name));
                            if (value == null) {
                                value = "";
                            }
                            final GenericAttribute attribute = GenericAttribute
                                    .obtain(value, objPropInfo);
                            genericObject.put(objPropInfo.getFullColumnName(),
                                    attribute);
                        }
                    }
                    genericObjectList.add(genericObject);
                    cursor.moveToNext();
                }
                cursor.close();
                cursor = null;
                return genericObjectList;
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "Load data",
                    getClass());
        }
        return null;
    }

    private void submitTransaction() {
        if ((this.mDatabase != null) && this.mDatabase.isOpen()) {
            if (this.mDatabase.inTransaction()) {
                if (this.mAllowSubmitTransaction) {
                    this.mDatabase.setTransactionSuccessful();
                    this.mDatabase.endTransaction();
                }
            }
        }
    }

    @Override
    public String update(final GenericObject object,
            final ContentData objPropCol) {
        try {
            final String mainTable = objPropCol.getMainTable();
            beginTransaction();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            final String id = baseUpdate(objPropCol, mainTableProperty, object);
            if (id == null) {
                LogUtil.info(LogUtil.TAG_DATABASE,
                        " can't update " + object.getTableName()
                                + "  to database", "update");
            } else {
                LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                        + " was updated, id=" + id, "update");
            }
            return id;
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "update object", getClass());
        } finally {
            submitTransaction();
        }
        return null;
    }

    @Override
    public String update(final GenericObject object,
            final ContentData objPropCol,
            final ArrayList<GenericObject> deletes,
            final ArrayList<GenericObject> inserts,
            final ContentData galleryContentData) {
        try {
            final String mainTable = objPropCol.getMainTable();
            beginTransaction();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            final String id = baseUpdate(objPropCol, mainTableProperty, object);
            if (id == null) {
                LogUtil.info(LogUtil.TAG_DATABASE,
                        " can't insert " + object.getTableName()
                                + "  to database", "insertObject");
            } else {
                LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                        + " was update, id=" + id, "insertObject");
            }

            for (final GenericObject deleteImage : deletes) {
                baseDelete(galleryContentData, galleryContentData, deleteImage);
            }

            final String column = galleryContentData.getMainTable() + "."
                    + objPropCol.getPrimaryKey(mainTable).getColumnName();

            for (final GenericObject insertImage : inserts) {
                insertImage.put(column, id);
                baseInsert(galleryContentData, galleryContentData, insertImage);
            }
            return id;
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "Insert object", getClass());
        } finally {
            submitTransaction();
        }
        return null;
    }

    @Override
    public ArrayList<String> updateList(
            final ArrayList<GenericObject> genericObjects,
            final ContentData objPropCol) {
        final ArrayList<String> ids = new ArrayList<String>();
        try {
            beginTransaction();
            final String mainTable = objPropCol.getMainTable();
            final ContentData mainTableProperty = GenericFilter
                    .getObjPropColOf(mainTable, objPropCol);
            for (final GenericObject object : genericObjects) {
                final String id = baseUpdate(objPropCol, mainTableProperty,
                        object);
                if (id == null) {
                    LogUtil.info(LogUtil.TAG_DATABASE, " can't update "
                            + object.getTableName() + "  to database",
                            "updateList");
                } else {
                    LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
                            + " was update, id=" + id, "updateList");
                    ids.add(id);
                }
            }
        } catch (final SQLException e) {
            LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
                    "update object", getClass());
        } finally {
            submitTransaction();
        }
        return ids;
    }
}
